﻿namespace Pub.Class
{
    using System;
    using System.Runtime.InteropServices;
    using System.Text;

    public class RowNumberPagerSQL : IPagerSQL
    {
        public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
        {
            PagerSql sql = new PagerSql();
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.AppendFormat("count({0}) as total ", pk);
            if (!tableName.IsNullEmpty())
            {
                builder.AppendFormat("from {0} ", tableName);
            }
            if (!where.IsNullEmpty())
            {
                builder.AppendFormat("where {0} ", where);
            }
            if (!groupBy.IsNullEmpty())
            {
                builder.AppendFormat("group by {0} ", groupBy);
            }
            sql.CountSql = builder.ToString();
            builder.Clear();
            builder.Append("select ");
            builder.AppendFormat("{0} ", fieldList);
            if (!tableName.IsNullEmpty())
            {
                builder.Append("from ( ");
                builder.AppendFormat("select {0}, row_number() over (order by {1}) as rownum ", fieldList, orderBy);
                builder.AppendFormat("from {0} ", tableName);
                if (!where.IsNullEmpty())
                {
                    builder.AppendFormat("where {0} ", where);
                }
                if (!groupBy.IsNullEmpty())
                {
                    builder.AppendFormat("group by {0} ", groupBy);
                }
                builder.AppendFormat(") as tmpTable where rownum > {0} and rownum <= {1}", (pageIndex - 1) * pageSize, ((pageIndex - 1) * pageSize) + pageSize);
            }
            sql.DataSql = builder.ToString();
            return sql;
        }
    }
}

